﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_MovePosition]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_MovePosition];
GO
CREATE PROCEDURE [dbo].[sproc_MovePosition] 
    @FromPositionID int,
    @ToPositionID int
/*

============================================================
功能:    移动职位
参数:
    @FromPositionID int        :    被移动的源职位ID
    @ToPositionID int        :    移动到的目标职位ID

============================================================

*/
AS
--判断@ToPositionID是否是@FromPositionID的子ID
DECLARE @id     int
DECLARE @pid     int

--在使用临时表时加上，否则无法正确返回记录
SET NOCOUNT ON

SET @pid=@ToPositionID
SET @id =0

--得到所有被访问组的父职位
CREATE TABLE #pPosition (pid int)
INSERT INTO #pPosition VALUES(@pid)
WHILE (@pid<>@id)
BEGIN
    SET @id = @pid
    SELECT @pid = super_Position_id FROM uds_Position WHERE Position_id = @id
    INSERT INTO #pPosition VALUES(@id)    
End

IF EXISTS(SELECT 1 FROM #pPosition WHERE pid = @FromPositionID)
BEGIN
    PRINT '不能移动到子项目中去~~'
    RETURN 
END

UPDATE uds_Position 
    SET super_Position_id = @ToPositionID 
    WHERE Position_id = @FromPositionID

SET NOCOUNT OFF